package cn.com.surker.database;

import cn.com.surker.base.system.entity.SysLine;
import cn.com.surker.gps.entity.GpsDataSource;
import cn.com.surker.system.entity.DataSourceInfo;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.scheduling.annotation.EnableScheduling;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Component;
import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.DefaultTransactionDefinition;
import org.springframework.util.StringUtils;

import javax.sql.DataSource;
import java.text.ParseException;
import java.util.*;
import java.util.concurrent.ConcurrentHashMap;

/**
 * @Author lucky
 * @create 2021年04月26 10:57
 * @desc
 */
@Component
@EnableScheduling
public class JDBCTemplateCache {

	//	设置连接超时时间,单位秒
	public static final int TIMEOUT_SECOND = 5;

	private static Map<String, JdbcTemplate> jdbcCacheMap = new ConcurrentHashMap<String, JdbcTemplate>();// 缓存容器


    public static Map<String, Date> jdbcTimeMap= Collections.synchronizedMap(new HashMap<String, Date>());

    //	事务状态	key:线
    protected static Map<String, TransactionStatus> tranStatusCache = new ConcurrentHashMap<>();
    //	事务管理器	key: 线
    private static Map<String, DataSourceTransactionManager> tranManageCache = new ConcurrentHashMap<>();
    /**
     * 获取阿里云数据源信息(查询操作)
     * @param info
     * @return
     */
    private static DataSource primaryDataSource(DataSourceInfo info) {
        DataSourceBuilder<?> dataSourceBuilder = DataSourceBuilder.create();
        dataSourceBuilder.driverClassName(info.getDriver());
        dataSourceBuilder.password(info.getPassword());
        dataSourceBuilder.url(info.getUrl());
        dataSourceBuilder.username(info.getUsername());
        return dataSourceBuilder.build();
    }

    /**
     * 获取GPS数据源信息(查询操作)
     * @param info
     * @return
     */
    private static DataSource primaryGPSDataSource(GpsDataSource info) {
        DataSourceBuilder<?> dataSourceBuilder = DataSourceBuilder.create();
        dataSourceBuilder.driverClassName(info.getDriver());
        dataSourceBuilder.password(info.getPassword());
        dataSourceBuilder.url(info.getUrl());
        dataSourceBuilder.username(info.getUsername());
        return dataSourceBuilder.build();
    }

    /**
     * 获取本地数据库数据源信息(URD操作)
     * @param info
     * @return
     */
    private static DataSource primaryLocalDataSource(DataSourceInfo info) {
        DataSourceBuilder<?> dataSourceBuilder = DataSourceBuilder.create();
        dataSourceBuilder.driverClassName(info.getLocalDriver());
        dataSourceBuilder.password(info.getLocalPassword());
        dataSourceBuilder.url(info.getLocalUrl());
        dataSourceBuilder.username(info.getLocalUsername());
        return dataSourceBuilder.build();
    }

    /**
     * 获取工控机本地数据库数据源信息(URD操作)
     * @param line
     * @return
     */
    private static DataSource primaryLineDataSource(SysLine line) {
        DataSourceBuilder<?> dataSourceBuilder = DataSourceBuilder.create();
        dataSourceBuilder.driverClassName(line.getDriver());
        dataSourceBuilder.password(line.getPassword());
        dataSourceBuilder.url(line.getUrl());
        dataSourceBuilder.username(line.getUsername());
        return dataSourceBuilder.build();
    }

    /**
     * 获取jdbctemplate对象
     * @param line
     * @return
     */
    public static JdbcTemplate getLineJdbcTemplate(SysLine line){
        if(!StringUtils.isEmpty(line.getDriver()) && !StringUtils.isEmpty(line.getUsername()) && !StringUtils.isEmpty(line.getType())
                && !StringUtils.isEmpty(line.getPassword()) && !StringUtils.isEmpty(line.getUrl())){
            if(line.getType().intValue() == 2)
                line.setDriver("oracle.jdbc.driver.OracleDriver");
            else if(line.getType().intValue() == 1)
                line.setDriver("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            else if (line.getType().intValue() == 0)
                line.setDriver("com.mysql.jdbc.Driver");
            else
                return null;
        }

        String code = "line_" + line.getCode();
        if (jdbcCacheMap.containsKey(code) && jdbcCacheMap.get(code) != null) {
            JdbcTemplate jdbc = jdbcCacheMap.get(code);
            try {
                jdbc.execute("select 1");
                return jdbc;
            } catch (Exception e) {
                e.printStackTrace();
                DataSource dbs = primaryLineDataSource(line);
                jdbc = new JdbcTemplate(dbs);
                jdbcCacheMap.put(code, jdbc);
                return jdbc;
            }
        }
//            return jdbcCacheMap.get(info.getCode());

        DataSource dbs = primaryLineDataSource(line);
        if(dbs != null){
            JdbcTemplate jdbc = new JdbcTemplate(dbs);
            jdbcCacheMap.put(code, jdbc);
            return jdbc;
        }else{
            return null;
        }
    }

    /**
     * 获取jdbctemplate对象
     * @param info
     * @return
     */
    public static JdbcTemplate getJdbcTemplate(DataSourceInfo info){
        if(!StringUtils.isEmpty(info.getDriver()) && !StringUtils.isEmpty(info.getUsername()) && !StringUtils.isEmpty(info.getType())
                && !StringUtils.isEmpty(info.getPassword()) && !StringUtils.isEmpty(info.getUrl())){
            if(info.getType().intValue() == 2)
                info.setDriver("oracle.jdbc.driver.OracleDriver");
            else if(info.getType().intValue() == 1)
                info.setDriver("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            else if (info.getType().intValue() == 0)
                info.setDriver("com.mysql.jdbc.Driver");
            else
                return null;
        }

        String dbKey = info.getCode() + "_" + cn.com.surker.util.StringUtils.convertInteger(info.getType());
        if (jdbcCacheMap.containsKey(dbKey) && jdbcCacheMap.get(dbKey) != null) {
            JdbcTemplate jdbc = jdbcCacheMap.get(dbKey);
            try {
                jdbc.execute("select 1");
                return jdbc;
            } catch (Exception e) {
                e.printStackTrace();
                DataSource dbs = primaryDataSource(info);
                jdbc = new JdbcTemplate(dbs);
                jdbcCacheMap.put(dbKey, jdbc);
                return jdbc;
            }
        }
//            return jdbcCacheMap.get(info.getCode());

        DataSource dbs = primaryDataSource(info);
        if(dbs != null){
            JdbcTemplate jdbc = new JdbcTemplate(dbs);
            jdbcCacheMap.put(dbKey, jdbc);
            return jdbc;
        }else{
            return null;
        }
    }

    /**
     * 获取jdbctemplate对象
     * @param info
     * @return
     */
    public static JdbcTemplate getGPSJdbcTemplate(GpsDataSource info){
        if(!StringUtils.isEmpty(info.getDriver()) && !StringUtils.isEmpty(info.getUsername()) && !StringUtils.isEmpty(info.getType())
                && !StringUtils.isEmpty(info.getPassword()) && !StringUtils.isEmpty(info.getUrl())){
            if(info.getType().intValue() == 2)
                info.setDriver("oracle.jdbc.driver.OracleDriver");
            else if(info.getType().intValue() == 1)
                info.setDriver("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            else if (info.getType().intValue() == 0)
                info.setDriver("com.mysql.jdbc.Driver");
            else
                return null;
        }

        String redisCode = "GPS_" + info.getCode();
        if (jdbcCacheMap.containsKey(redisCode) && jdbcCacheMap.get(redisCode) != null)
            return jdbcCacheMap.get(redisCode);

        DataSource dbs = primaryGPSDataSource(info);
        if(dbs != null){
            JdbcTemplate jdbc = new JdbcTemplate(dbs);
            jdbcCacheMap.put(redisCode, jdbc);
            return jdbc;
        }else{
            return null;
        }
    }

    /**
     * 获取jdbctemplate对象
     * @param info
     * @return
     */
    public static JdbcTemplate getLocalJdbcTemplate(DataSourceInfo info){
        if(!StringUtils.isEmpty(info.getLocalDriver()) && !StringUtils.isEmpty(info.getLocalUsername())
                && !StringUtils.isEmpty(info.getLocalType())
                && !StringUtils.isEmpty(info.getLocalPassword()) && !StringUtils.isEmpty(info.getLocalUrl())){
            if(info.getType().intValue() == 2)
                info.setLocalDriver("oracle.jdbc.driver.OracleDriver");
            else if(info.getType().intValue() == 1)
                info.setLocalDriver("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            else if (info.getType().intValue() == 0)
                info.setLocalDriver("com.mysql.jdbc.Driver");
            else
                return null;
        }

        String redisCode = "LOCAL_" + info.getCode() + "_" + info.getType();
        if (jdbcCacheMap.containsKey(redisCode) && jdbcCacheMap.get(redisCode) != null) {
            JdbcTemplate jdbc = jdbcCacheMap.get(redisCode);
            try {
                jdbc.execute("select 1");
                return jdbc;
            } catch (Exception e) {
                jdbcCacheMap.remove(redisCode);
                e.printStackTrace();
//                DataSource dbs = primaryLocalDataSource(info);
//                jdbc = new JdbcTemplate(dbs);
//                jdbcCacheMap.put(redisCode, jdbc);
                return null;
            }
        }

        DataSource dbs = primaryLocalDataSource(info);
        if(dbs != null){
            JdbcTemplate jdbc = new JdbcTemplate(dbs);
            jdbcCacheMap.put(redisCode, jdbc);
            return jdbc;
        } else {
            return null;
        }
    }

    /**
     * 定时检查jdbc对象数
     * @throws ParseException
     */
    @Scheduled(cron = "0/30 * * * * ?")
    private void configureTasks() throws ParseException {
        int count = jdbcCacheMap.size();
        if(count > 20){
            Date now = new Date();
            for(Iterator<String> iterator = jdbcTimeMap.keySet().iterator(); iterator.hasNext(); ) {
                String key = iterator.next();
                int hours = differentHour(jdbcTimeMap.get(key), now);
                if(hours > 1){
                    jdbcCacheMap.remove(key);
                    iterator.remove();
                }
            }
        }
        System.gc();
    }

    public synchronized static Integer differentHour(Date date1, Date date2) {
        Long diff = date2.getTime() - date1.getTime();
        Long diffDay = 1000 * 24 * 60 * 60L;
        Long diffHour = 1000 * 60 * 60L;
        Long h = diff % diffDay / diffHour;
        Integer hour = Integer.valueOf(h.toString());
        return hour;
    }

	//==================用于事务的维护手动控制===================
    /**
     * 获取生产线的数据连接
     *
     * @param line
     * @return
     */
    public static JdbcTemplate getLineTransactionJdbcTemp(SysLine line) {
        if (line == null) {
            return null;
        }
        DataSourceInfo lineDs = new DataSourceInfo();
        lineDs.setDriver(line.getDriver());
        lineDs.setUsername(line.getUsername());
        lineDs.setType(line.getType());
        lineDs.setPassword(line.getPassword());
        lineDs.setUrl(line.getUrl());
        JdbcTemplate jdbcTemplate = JDBCTemplateCache.getLineJdbcTemplate(line);
        if (jdbcTemplate == null) {
            //	没有连接
            return null;
        }
        //	初始化事务管理
        DataSource dataSource = jdbcTemplate.getDataSource();
        //	查询之前是否存在过
        String key = "line_" + line.getId();
        if (!cn.com.surker.util.StringUtils.isNull(line.getTransactionKey()))
            key = "line_" + line.getTransactionKey();
        DataSourceTransactionManager oldDSTranManager = tranManageCache.get(key);
        if (oldDSTranManager == null) {
            DataSourceTransactionManager transactionManager = JDBCTemplateCache.getTransactionManager(dataSource);
            tranManageCache.put(key, transactionManager);
        }
        return jdbcTemplate;
    }

    /**
     *
     * @param info
	 * @param connType 0云端，1本地
     * @return
     */
	public static JdbcTemplate getInfoTransactionJdbcTemp(DataSourceInfo info, Integer connType) {
        if (info == null) {
            return null;
        }
		JdbcTemplate jdbcTemplate = null;
		if (cn.com.surker.util.StringUtils.isNull(connType)) {
			connType = 0;
		}
		String code = info.getCode();
		//	默认生产线
		String key = "";
		if (connType == 0) {
			//	生产线
			key = "line_" + code;
		} else if (connType == 1) {
			//	云端
			key = "info_" + code;
			jdbcTemplate = getJdbcTemplate(info);
		} else if (connType == 2) {
			//	本地
			key = "local_" + code;
			jdbcTemplate = getLocalJdbcTemplate(info);
		}

        if (jdbcTemplate == null) {
            //	没有连接
            return null;
        }
        //	初始化事务管理
        DataSource dataSource = jdbcTemplate.getDataSource();
        //	查询之前是否存在过
        DataSourceTransactionManager oldDSTranManager = tranManageCache.get(key);
        if (oldDSTranManager == null) {
            DataSourceTransactionManager transactionManager = JDBCTemplateCache.getTransactionManager(dataSource);
            tranManageCache.put(key, transactionManager);
        }
        return jdbcTemplate;
    }
	/**
	 * 装配事务管理器
	 *
	 * @return
	 */
	public static DataSourceTransactionManager getTransactionManager(DataSource dataSource) {
		return new DataSourceTransactionManager(dataSource);
	}

	/**
	 * 开启事务
	 */
	public static TransactionStatus beginTransaction(DataSourceTransactionManager transactionManager) {
		DefaultTransactionDefinition def = new DefaultTransactionDefinition();    //事务定义类
		def.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRED);
		TransactionStatus status = transactionManager.getTransaction(def);        // 返回事务对象
		return status;
	}

	/**
	 * 提交事务
	 *
	 * @param transactionManager
	 * @param status
	 */
	public static void commitTransaction(DataSourceTransactionManager transactionManager, TransactionStatus status) {
		transactionManager.commit(status);
	}

	/**
	 * 事务回滚
	 *
	 * @param transactionManager
	 * @param status
	 */
	public static void rollbackTransaction(DataSourceTransactionManager transactionManager, TransactionStatus status) {
		transactionManager.rollback(status);
	}


    /**
     * 开启事务
     * @param connType 0生产线 1 云端info 2,本地info
     */
    public static boolean beginTransaction(String code, Integer connType) {
		/*if(transactionStatus == null){
			transactionStatus = JDBCTemplateCache.beginTransaction(transactionManager);
		}*/
		if (cn.com.surker.util.StringUtils.isNull(connType))
            connType =0;
		String key = "line_" + code;
		if (connType == 1)
		    key = "info_" + code;
		else if (connType == 2)
		    key = "local_" + code;

        TransactionStatus transactionStatus = tranStatusCache.get(key);
        if (transactionStatus == null) {
            //	没有开启过
            DataSourceTransactionManager transactionManager = tranManageCache.get(key);
            transactionStatus = beginTransaction(transactionManager);
            tranStatusCache.put(key, transactionStatus);
            return true;
        }
        return false;
    }

    /**
     * 提交事务
     */
    public static void commitTranLine(String code, Integer connType) {
        if (cn.com.surker.util.StringUtils.isNull(connType))
            connType =0;
        String key = "line_" + code;
        if (connType == 1)
            key = "info_" + code;
        else if (connType == 2)
            key = "local_" + code;
        TransactionStatus transactionStatus = tranStatusCache.get(key);
        if (transactionStatus != null) {
            DataSourceTransactionManager transactionManager = tranManageCache.get(key);
            JDBCTemplateCache.commitTransaction(transactionManager, transactionStatus);
            tranStatusCache.remove(key);
        }
    }

    /**
     * 回滚事务
     */
    public static void rollbackLine(String code, Integer connType) {
        if (cn.com.surker.util.StringUtils.isNull(connType))
            connType =0;
        String key = "line_" + code;
        if (connType == 1)
            key = "info_" + code;
        else if (connType == 2)
            key = "local_" + code;
        TransactionStatus transactionStatus = tranStatusCache.get(key);
        if (transactionStatus != null) {
            DataSourceTransactionManager transactionManager = tranManageCache.get(key);
            JDBCTemplateCache.rollbackTransaction(transactionManager, transactionStatus);
            //	清空
            tranStatusCache.remove(key);
        }
    }

    public static void commitTranAll() {
        Set<String> tranSet = tranStatusCache.keySet();
        for (String code : tranSet) {
            TransactionStatus transactionStatus = tranStatusCache.get(code);
            if (transactionStatus != null) {
                DataSourceTransactionManager transactionManager = tranManageCache.get(code);
                JDBCTemplateCache.commitTransaction(transactionManager, transactionStatus);
                tranManageCache.remove(code);
            }
        }
    }

    public static void rollbackAll() {
        Set<String> tranSet = tranStatusCache.keySet();
        for (String code : tranSet) {
            TransactionStatus transactionStatus = tranStatusCache.get(code);
            if (transactionStatus != null) {
                DataSourceTransactionManager transactionManager = tranManageCache.get(code);
                JDBCTemplateCache.rollbackTransaction(transactionManager, transactionStatus);
                //	清空
                tranStatusCache.remove(code);
            }
        }
    }
}
